/**
 * 
 */
package dataPersistence.dao;

import hitExceptions.InvalidDataException;
import hitExceptions.InvalidOperationException;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashSet;

import common.Result;
import common.ValueResult;
import coreModel.Barcode;
import coreModel.HIT;
import coreModel.Item;
import coreModel.Product;
import coreModel.ProductContainer;
import coreModel.StorageUnit;
import coreModel.Supply;
import coreModel.Unit;

import dataPersistence.TransactionManager;
import dataPersistence.dto.ItemDTO;
import dataPersistence.dto.ProductDTO;

/**
 * @author fantastic4
 *
 */
public class Item_DB_DAO implements IItemDAO
{
	@Override
	public Result add(ItemDTO dto)
	{
		Result result = new Result();
		try 
		{

			String query = "insert into Item (barcode, productBarcode, entryDate, exitDate, container_ID) values (?, ?, ?, ?, ?)";
			PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setString(1, dto.getBarcode());
			stmt.setString(2, dto.getProductBarcode());
			stmt.setDate(3, new Date(dto.getEntryDate().getTime())); // convert from util.Date to sql.Date
			if (dto.getExitDate() != null)
				stmt.setDate(4, new Date(dto.getExitDate().getTime())); // convert from util.Date to sql.Date
			else
				stmt.setNull(4,java.sql.Types.DATE);
			stmt.setString(5, dto.getContainer()); //TODO: remove???

			if (stmt.executeUpdate() == 1)
				result.setStatus(true);
			else
				result.setMessage("count not add an item");
		}
		catch (SQLException e) 
		{
			result.setMessage(e.getMessage()+" in add item");

		}
		return result;
	}

	@Override
	public Result update(ItemDTO dto)
	{

		Result result = new Result();
		try 
		{
			String query = "update Item set entryDate=?, exitDate=?, container_ID=?, productBarcode=? where barcode=?";
			PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setDate(1, new Date(dto.getEntryDate().getTime())); // convert from util.Date to sql.Date
			if(dto.getExitDate()!=null)
				stmt.setDate(2, new Date(dto.getExitDate().getTime())); // convert from util.Date to sql.Date
			else
				stmt.setNull(2, java.sql.Types.DATE);

			stmt.setString(3, dto.getContainer());
			stmt.setString(4, dto.getProductBarcode());
			stmt.setString(5, dto.getBarcode());

			if (stmt.executeUpdate() == 1)
				result.setStatus(true);
			else
			{
				result.setMessage("Could not update member");

			}

		}
		catch (SQLException e) 
		{
			result.setMessage(e.getMessage());
		}
		return result;
	}

	@Override
	public Result delete(ItemDTO dto)
	{
		System.out.println("in here");
		Result result = new Result();
		try
		{
			String query = "delete from Item where barcode=?";
			PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setString(1, dto.getBarcode());
			if (stmt.executeUpdate() == 1)
				result.setStatus(true);
			else
				result.setMessage("Could not delete member");
		}
		catch (SQLException e) 
		{
			result.setMessage(e.getMessage());
		}
		return result;
	}

	public ValueResult<Collection<ItemDTO>> getAllItems()
	{
		String query = "select barcode, productBarcode, entryDate, exitDate, container_ID from Item where exitDate IS NULL";

		return getAllItems(query);
	}
	
	public ValueResult<Collection<ItemDTO>> getAllDeleteItems()
	{
		String query = "select barcode, productBarcode, entryDate, exitDate, container_ID from Item where exitDate IS NOT NULL";

		return getAllItems(query);
	}
	
	
	private ValueResult<Collection<ItemDTO>> getAllItems(String queryString)
	{
		ValueResult<Collection<ItemDTO>> result = new ValueResult<Collection<ItemDTO>>();

		PreparedStatement stmt = null;
		ResultSet rs=null;
		Collection<ItemDTO> allItems = new ArrayList<ItemDTO>();
		try 
		{
			String query = queryString;
			stmt = TransactionManager.getConnection().prepareStatement(query);

			rs = stmt.executeQuery();
			while (rs.next())
			{
				ItemDTO tmp = new ItemDTO();
				tmp.setBarcode(rs.getString(1));
				tmp.setProductBarcode(rs.getString(2));
				tmp.setEntryDate(rs.getDate(3)); 
				tmp.setExitDate(rs.getDate(4)); 
				tmp.setContainer(rs.getString(5));
				allItems.add(tmp);
			}
			result.setValue(allItems);
			result.setStatus(true);

		}
		catch (SQLException e) 
		{
			result.setMessage(e.getMessage());
		}		
		finally {
			TransactionManager.safeClose(rs);
			TransactionManager.safeClose(stmt);
		}

		return result;
	}


	//test driver
	public static void main(String[] var)
	{
		try {
			StorageUnit pc = new StorageUnit("ProdContainer PC");
			Product p = new Product(new Barcode("Pbarcode"), "Product P", 3, 0, 
					new Supply(1, Unit.OUNCES));
			HIT.getInstance().setLoadingFromDataBase(true);
			HIT.getInstance().addStorageUnit(pc);
			HIT.getInstance().getProductManager().addProductToContainer(p, pc);
			HIT.getInstance().setLoadingFromDataBase(false);

			Item i = new Item(new Barcode("0000123400005678"), p, pc);
			i.setEntryDate(new java.util.Date());

			TransactionManager.initialize();
			TransactionManager.begin();
			Item_DB_DAO dao = new Item_DB_DAO();
			dao.add(new ItemDTO(i));
			TransactionManager.end(true);

		} catch (InvalidDataException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InvalidOperationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
